USE UnemDB;
GO

CREATE SCHEMA Unem
GO

CREATE TABLE Unem.Menus
(
	menuId int IDENTITY PRIMARY KEY,
	orgId int FOREIGN KEY REFERENCES Unem.Organizations(orgId) ON DELETE CASCADE,
	restId int FOREIGN KEY REFERENCES Unem.Restaurants(restId) ON DELETE CASCADE,
	menuPublished datetime2
)
GO
CREATE TABLE Unem.Items
(
	itemId int IDENTITY PRIMARY KEY,
	menuId int FOREIGN KEY REFERENCES Unem.Menus(menuId) ON DELETE CASCADE,
	itemName nvarchar(255),
	itemImage nvarchar(255),
	itemDescription nvarchar(255),
	itemPrice numeric(10,2),
	typeId int FOREIGN KEY REFERENCES Unem.Types(typeId) ON DELETE CASCADE,
	itemPublished datetime2
)
GO

CREATE TABLE Unem.Organizations
(
	orgId int IDENTITY PRIMARY KEY,
	orgName nvarchar(30),
	orgDescription nvarchar(max),
	contId int FOREIGN KEY REFERENCES Unem.Contacts(contId) ON DELETE CASCADE,
	userId int FOREIGN KEY REFERENCES Unem.Users(userId) ON DELETE CASCADE,
	added datetime2
)
GO
CREATE TABLE Unem.Restaurants
(
	restId int IDENTITY PRIMARY KEY,
	orgId int FOREIGN KEY REFERENCES Unem.Organizations(orgId) ON DELETE CASCADE,
	restName nvarchar(30),
	restDescription nvarchar(max),
	contId int FOREIGN KEY REFERENCES Unem.Contacts(contId) ON DELETE CASCADE,
	added datetime2
)
GO
CREATE TABLE Unem.Contacts
(
	contId int IDENTITY PRIMARY KEY,
	contEmail nvarchar(30),
	contPhone nvarchar(20),
	contSite nvarchar(50),
	contAddress nvarchar(100),
	contImage nvarchar(255)
	added datetime2
)
GO
CREATE TABLE Unem.Types
(
	typeId int IDENTITY PRIMARY KEY,
	typeName nvarchar(30),
	added datetime2
)
GO
CREATE TABLE Unem.Users
(
	userId int IDENTITY PRIMARY KEY,
	login nvarchar(12),
	email nvarchar(50),
	password nvarchar(50),
	firstName nvarchar(50),
	lastName nvarchar(50),
	userType int,
	regDate datetime2
)